<?php

require_once dirname(dirname(__FILE__)) . '/config/config.php';
require_once DOCUMENT_ROOT . DIR_SHARED_CONFIG . 'connection.php';


class dao_booking {

    //Methods
    public function __construct() {

    }

    public function get_all_paid() {
        $db = new connection();
        $con = $db->open_connect();
        
        $query = "SELECT ms.email AS user_email, CONCAT_WS(' ', ms.first_name, ms.last_name) AS user_full_name, "
                . "tk.name AS ticket_name, cp.coupon_code AS coupon_code, "
                . "cb.name AS combo_name, m.* "
                . "FROM tbl_booking m LEFT JOIN tbl_user ms ON m.user_id = ms.id "
                . "LEFT JOIN tbl_ticket tk ON m.ticket_id = tk.id "
                . "LEFT JOIN tbl_coupon cp ON m.coupon_id = cp.id "
                . "LEFT JOIN tbl_combo cb ON m.combo_id = cb.id "
                . "WHERE is_paid = 1";
        $result = mysqli_query($con, $query)
        or die("Query fail: " . mysqli_error());
        
        $list = array();
        while ($row = mysqli_fetch_array($result)) {
            array_push($list, $row);
        }
        $db->close_connect();
        return $list;
    }
    
    public function get_all() {
        $db = new connection();
        $con = $db->open_connect();
        
        $query = "SELECT ms.email AS user_email, CONCAT_WS(' ', ms.first_name, ms.last_name) AS user_full_name, "
                . "tk.name AS ticket_name, cp.coupon_code AS coupon_code, "
                . "cb.name AS combo_name, m.* "
                . "FROM tbl_booking m LEFT JOIN tbl_user ms ON m.user_id = ms.id "
                . "LEFT JOIN tbl_ticket tk ON m.ticket_id = tk.id "
                . "LEFT JOIN tbl_coupon cp ON m.coupon_id = cp.id "
                . "LEFT JOIN tbl_combo cb ON m.combo_id = cb.id";
        $result = mysqli_query($con, $query)
        or die("Query fail: " . mysqli_error());
        
        $list = array();
        while ($row = mysqli_fetch_array($result)) {
            array_push($list, $row);
        }
        $db->close_connect();
        return $list;
    }
    
    public function get_all_by_status_id($status_id) {
        $db = new connection();
        $con = $db->open_connect();
        
        $query = "SELECT ms.name AS status_name, m.* "
                . "FROM tbl_booking m LEFT JOIN tbl_booking_status ms ON m.status_id = ms.id "
                . "WHERE ms.id = " . $status_id;
        $result = mysqli_query($con, $query)
        or die("Query fail: " . mysqli_error());
        
        $list = array();
        while ($row = mysqli_fetch_array($result)) {
            array_push($list, $row);
        }
        $db->close_connect();
        return $list;
    }

    public function update_generated_code($id, $code) {
        $db = new connection();
        $con = $db->open_connect();
        
        $query = "UPDATE tbl_booking SET "
                . "generated_code = '" . $code . "' "
                . "WHERE id = " . $id;
        $result = mysqli_query($con, $query) or die("Query fail: " . mysqli_error());
        $db->close_connect();
        return TRUE;
    }
    
    public function update_paid($id) {
        $db = new connection();
        $con = $db->open_connect();
        
        $query = "UPDATE tbl_booking SET "
                . "is_paid = 1 "
                . "WHERE id = " . $id;
        $result = mysqli_query($con, $query) or die("Query fail: " . mysqli_error());
        $db->close_connect();
        return TRUE;
    }
    
    public function save($id, $user_id, $ticket_id, 
                $ticket_quantity_solve, $coupon_id, $combo_id, 
                $combo_quantity_solve, $total_money, $paid) {
        $db = new connection();
        $con = $db->open_connect();
        
        if ($id == 0) {
            $query = "INSERT INTO tbl_booking(user_id, ticket_id, ticket_quantity, coupon_id, combo_id, combo_quantity, total_money, is_paid) VALUES (
                " . $user_id . "," . $ticket_id . "," . $ticket_quantity_solve . "," . $coupon_id . "," . $combo_id . "," . $combo_quantity_solve . "," . $total_money . "," . $paid . ")";
        } else {
            $query = "UPDATE tbl_booking SET "
                    . "user_id = '" . $user_id . "',"
                    . "ticket_id = " . $ticket_id . ","
                    . "ticket_quantity = '" . $ticket_quantity_solve . "',"
                    . "coupon_id = '" . $coupon_id . "',"
                    . "combo_id = '" . $combo_id . "',"
                    . "combo_quantity = '" . $combo_quantity_solve . "',"
                    . "total_money = '" . $total_money . "', "
                    . "is_paid = " . $paid . " "
                    . "WHERE id = " . $id;
        }
        $result = mysqli_query($con, $query) or die("Query fail: " . mysqli_error());
        $inserted_id = TRUE;
        if ($id == 0) {
            $inserted_id = mysqli_insert_id($db->con);
        }
        $db->close_connect();
        return $inserted_id;
    }
    
    public function get_by_id($id) {
        $db = new connection();
        $con = $db->open_connect();

        $query = "SELECT ms.email AS user_email, CONCAT_WS(' ', ms.first_name, ms.last_name) AS user_full_name, "
                . "tk.name AS ticket_name, cp.coupon_code AS coupon_code, "
                . "cb.name AS combo_name, m.* "
                . "FROM tbl_booking m LEFT JOIN tbl_user ms ON m.user_id = ms.id "
                . "LEFT JOIN tbl_ticket tk ON m.ticket_id = tk.id "
                . "LEFT JOIN tbl_coupon cp ON m.coupon_id = cp.id "
                . "LEFT JOIN tbl_combo cb ON m.combo_id = cb.id WHERE m.id = " . $id;
        $result = mysqli_query($con, $query) or die("Query fail: " . mysqli_error());

        $row = mysqli_fetch_array($result);

        $db->close_connect();
        return $row;
    }
    public function delete($id) {
        $db = new connection();
        $con = $db->open_connect();

        $query = "DELETE FROM tbl_booking WHERE id = " . $id;
        mysqli_query($con, $query) or die("Query fail: " . mysqli_error());

        $db->close_connect();
        return TRUE;
    }
    
    public function get_by_code($code) {
        $db = new connection();
        $con = $db->open_connect();

        $query = "SELECT * "
                . "FROM tbl_booking "
                . "WHERE generated_code = '" . $code . "'";
        $result = mysqli_query($con, $query) or die("Query fail: " . mysqli_error());

        $row = mysqli_fetch_array($result);

        $db->close_connect();
        return $row;
    }
}
